databasemanagementfandomcom-20200214-history
PL/SQL
PL/SQL stands for "Procedural Language extensions to SQL", and is a procedural language used while working with Oracle databases. PL/SQL is directly related to classic SQL; however it adds useful iterative, selective and sequential programming constructs that can be used in combination with traditional SQL statements. The combination of structural programming methods and SQL creates a powerful application development and data management utility. PL/SQL programs are based on blocks. Each programmatic block can be stand-alone or can contain other blocks. Blocks are structured and sequenced to perform logical operations in programs. Blocks are ordered: Declaration of working memory, Execution section using working memory, and Exception handling. PL/SQL can be used to create ad-hawk data retrieval or manipulation scripts, or as procedures and functions in a more consistent implementation. A typical PL/SQL (script-style) program is structured as follows: Declare… Declaration section used for working single-value variables, SQL Cursors, and user-defined data types. (Optional block) Begin… Program logic execution section: This section can contain selection constructs like IF-THEN-ELSE-ELSIF-END IF and SWICH, and iterative constructs such as LOOP-END LOOP and FETCH-until-NOTFOUND. This section can call and pass parameters to stored functions and procedures. (Required block) Exception… Exception handling section, can be nested (optional block) End; A typical PL/SQL (stored-procedure/function-style) program is the same with a different opening and closing as follows: Procedure/function name (pram1 in type, parm2 in type) is type is… Begin Exception... End; End name; Notice that only the executable section is required for scripts, however the opening and closing decelerations are required to compile the procedure and store it in the database. PL/SQL procedures are marked either as Valid if there are no errors or Invalid if there is any error during compilation. If any complied procedure is invalid then all dependent procedures are inherently invalid also. It is also worth mentioning that DDL commands are not allowed in PL/SQL and the only directly allowed SQL statements are SELECT, UPDATE, INSERT, and DELETE. Variables in PL/SQL THE DECLARE STATEMENT •To use variables, cursors, classes, or user data structures in PS/SQL they first must be declared. This is accomplished using the declare statement. Example: declare numeric ID and a string name variables DECLARE ID NUMBER; NAME VARCHAR2(50); BEGIN… NUMERIC TYPES •NUMBER •BINARY_FLOAT •BINARY_DOUBLE •DEC •DECIMAL •DOUBLE PRECISION •FLOAT •INTEGER •INT •NUMERIC •REAL •SMALLINT •BINARY_INTEGER •PLS_INTEGER STRING/CHARACTER TYPES •VARCHAR2 •VARCHAR •CHAR •LONG •RAW •NCHAR2 •NCHAR OTHER TYPES •BOOLEAN •DATE •USER DEFINED TYPES •ADVANCED USER DEFINED TYPES CURSORS •Cursors are used for iterative dataset processing. Cursors allow for row-by-row processing of a ‘Select query’ result set. The Fetch command allow for the variable assignment of each selected field. Cursors are designed to be used in one of several for-each-row style looping constructs. Example: Get Person ID, Name, and IQ from the person table and use that information to determine which intelligence category to place each person into and then insert their identity into the corresponding table. We will commit the additional records at a frequency of every 250 inserts. DECLARE RCNT NUMBER := 1; ID NUMBER; NAME VARCHAR2(30); IQ NUMBER; CURSOR CUR IS SELECT PERSON_ID, PERSON_NAME, PERSON_IQ FROM PERSON WHERE PERSON_CHANGE_IND IS NULL BEGIN OPEN CUR; LOOP FETCH CUR INTO ID,NAME,IQ; EXIT WHEN (CUR%NOTFOUND); IF IQ > 150 INSERT INTO GENIUSES(GENIUS _ID, GENIUS_NAME, GENIUS_IQ) VALUES(ID,NAME,IQ); ELSEIF IQ > 115 INSERT INTO SMARTIES(SMARTY_ID, SMARTY_NAME, SMARTY_IQ) VALUES(ID,NAME,IQ); ELSEIF IQ > 75 INSERT INTO NORMALS(PERSON_ID, PERSON_NAME, PERSON_IQ) VALUES(ID,NAME,IQ); ELSE INSERT INTO DUMMYS(DUMMY_ID,DUMMY_NAME,DUMMY_IQ) VALUES(ID,NAME,IQ); END IF; RCNT:=RCNT+1; IF RCNT = 250 THEN COMMIT; CCNT:= CCNT+1; RCNT:=0; END IF; END LOOP; CLOSE CUR; COMMIT; END;